using System;
using System.Collections.Generic;
using System.Text;
using DTOLayer;
using System.Collections;
using System.Data.OleDb;
using System.Data;

namespace BUSLayer
{
    public class SoTietKiemDAO
    {
        public static void ThemSoTietKiem(SoTietKiemDTO stk)
        {
            //B1&B2 Tao chuoi ket noi va ket noi bang doi tuong ket noi
            OleDbConnection cn;
            cn = Database.ConnectionData();
            //B3 Tao chuoi strSQL ket noi CSDL
            string strSQL = "";

            strSQL = "Insert Into SOTIETKIEM(MaSo,MaLoaiTietKiem,HoTenKhachHang,CMND,DiaChi,NgayMoSo,SoTienGoi,SoLanDaoHan,TinhTrangSo,NgayDaoHan,LaiSuat,ThoiHanRutToiThieu) Values(?,?,?,?,?,?,?,?,?,?,?,?)";
            //B4 Thuc Thi chuoi strSQL
            OleDbCommand cmd = new OleDbCommand(strSQL, cn);

            cmd.Parameters.Add("@MaSo", OleDbType.LongVarChar);
            cmd.Parameters.Add("@MaLoaiTietKiem", OleDbType.LongVarChar);
            cmd.Parameters.Add("@HoTenKhachHang", OleDbType.VarChar);
            cmd.Parameters.Add("@CMND", OleDbType.VarChar);
            cmd.Parameters.Add("@DiaChi", OleDbType.VarChar);
            cmd.Parameters.Add("@NgayMoSo", OleDbType.Date);
            cmd.Parameters.Add("@SoTienGoi", OleDbType.Double);
            cmd.Parameters.Add("@SoLanDaoHan", OleDbType.Integer);
            cmd.Parameters.Add("@TinhTrangSo", OleDbType.Integer);
            cmd.Parameters.Add("@NgayDaoHan", OleDbType.Date);
            cmd.Parameters.Add("@LaiSuat", OleDbType.Double);
            cmd.Parameters.Add("@ThoiHanRutToiThieu", OleDbType.LongVarChar);



            cmd.Parameters[0].Value = stk.MaSo;
            cmd.Parameters[1].Value = stk.MaLoaiTK;
            cmd.Parameters[2].Value = stk.HoTenKhachHang;
            cmd.Parameters[3].Value = stk.CMND;
            cmd.Parameters[4].Value = stk.DiaChi;

            cmd.Parameters[5].Value = stk.NgayMoSo;
            cmd.Parameters[6].Value = stk.SoTienGoi;
            cmd.Parameters[7].Value = stk.SoLanDaoHan;
            cmd.Parameters[8].Value = stk.TinhTrangSo;
            cmd.Parameters[9].Value = stk.NgayDaoHan;

            cmd.Parameters[10].Value = stk.LaiSuat;
            cmd.Parameters[11].Value = stk.ThoiHanRutToiThieu;


            cmd.ExecuteNonQuery();

            //B5Dong ket noi
            cn.Close();
        }

        public static long MaxMaSoTietKiem()
        {
            //B1&B2 tao chuoi kiet noi
            // OleDbConnection cn;
            // cn = Database.ConnectionData();
            //B3 Tao chuoi ket noi CSDL
            string sql = "SELECT  Max(MaSo) From SOTIETKIEM";
            //B4 thuc thi chuoi sql
            // OleDbCommand cmd = new OleDbCommand(sql, cn);
            long kq;
            // kq= long.Parse( cmd.ExecuteScalar().ToString());
            //cn.Close();

            kq = Database.ExecuteScalar(sql);
            return kq;

        }

        public static List<SoTietKiemDTO> SelectAllSoTietKiemList()
        {
            try
            {

                List<SoTietKiemDTO> list = new List<SoTietKiemDTO>();
                //B1&B2 tao chuoi ket noi
                OleDbConnection cn;
                cn = Database.ConnectionData();
                //B3 tao chuoi ket noi CSDL
                string sql = "SELECT * From SOTIETKIEM";
                //b4 thuc thi chuoi ket noi sql
                OleDbCommand cmd = new OleDbCommand(sql, cn);
                OleDbDataReader dr;
                dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    SoTietKiemDTO stk = new SoTietKiemDTO();
                    stk.MaSo = long.Parse(dr["MaSo"].ToString());
                    stk.MaLoaiTK = long.Parse(dr["MaLoaiTietKiem"].ToString());
                    stk.HoTenKhachHang = dr["HoTenKhachHang"].ToString();
                    stk.CMND = dr["CMND"].ToString();
                    stk.DiaChi = dr["DiaChi"].ToString();
                    stk.NgayMoSo = DateTime.Parse(dr["NgayMoSo"].ToString());
                    stk.SoTienGoi = double.Parse(dr["SoTienGoi"].ToString());
                    stk.SoLanDaoHan = int.Parse(dr["SoLanDaoHan"].ToString());
                    stk.TinhTrangSo = int.Parse(dr["TinhTrangSo"].ToString());
                    stk.ThoiHanRutToiThieu = long.Parse(dr["ThoiHanRutToiThieu"].ToString());
                    stk.LaiSuat = double.Parse(dr["LaiSuat"].ToString());
                    stk.NgayDaoHan = DateTime.Parse(dr["NgayDaoHan"].ToString());

                    list.Add(stk);

                }
                cn.Close();
                return list;

            }
            catch (Exception ex)
            {
                throw ex;
            }

        }
        //lay so tiet kiem theo ma so
        public static SoTietKiemDTO LaySoTietKiemTheoMaSo(long maso)
        {
            SoTietKiemDTO stk = new SoTietKiemDTO();

            //B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
            OleDbConnection cn;
            cn = Database.ConnectionData();

            //B3: Tao chuoi strSQL thao tac CSDL
            string strSQL;
            strSQL = "Select * From SOTIETKIEM Where MaSo=" + maso.ToString();

            //B4: Thuc thi chuoi strSQL
            OleDbCommand cmd = new OleDbCommand(strSQL, cn);
            OleDbDataReader dr;
            dr = cmd.ExecuteReader();
            while (dr.Read())
            {
                stk.MaSo = long.Parse(dr["MaSo"].ToString());
                stk.MaLoaiTK = long.Parse(dr["MaLoaiTietKiem"].ToString());
                stk.HoTenKhachHang = (string)dr["HoTenKhachHang"];
                stk.CMND = (string)dr["CMND"];
                stk.DiaChi = (string)dr["DiaChi"];
                stk.NgayMoSo = DateTime.Parse(dr["NgayMoSo"].ToString());
                stk.SoTienGoi = double.Parse(dr["SoTienGoi"].ToString());
                stk.SoLanDaoHan = int.Parse(dr["SoLanDaoHan"].ToString());
                stk.TinhTrangSo = int.Parse(dr["TinhTrangSo"].ToString());
                stk.ThoiHanRutToiThieu = long.Parse(dr["ThoiHanRutToiThieu"].ToString());
                stk.LaiSuat = double.Parse(dr["LaiSuat"].ToString());
                stk.NgayDaoHan = DateTime.Parse(dr["NgayDaoHan"].ToString());
            }
            //B5: Dong ket noi CSDL

            cn.Close();
            return stk;
        }


        public static void Update_SoTienGoi_SoTietKiem(long maso,double sotiengoi)
        {

            //B1&B2 Tao chuoi ket noi va mo ket noi bang doi tuong ket noi
            OleDbConnection cn;
            cn = Database.ConnectionData();
            //B3 Tao chuoi strSQL thao tac CSDL
            string strSQL;

            strSQL = "UPDATE SOTIETKIEM  Set SoTienGoi=? Where Maso=?";


            //B4 Thuc Thi chuoi strSQL
            OleDbCommand cmd = new OleDbCommand(strSQL, cn);

            cmd.Parameters.Add("@SoTienGoi", OleDbType.Double);
            cmd.Parameters.Add("@MaSo", OleDbType.LongVarChar);

            cmd.Parameters["@SoTienGoi"].Value = sotiengoi;
            cmd.Parameters["@MaSo"].Value = maso;
            
           
            cmd.ExecuteNonQuery();
            //B5 Dong ket noi
            cn.Close();

        }
        public static void UpdateSoTietKiem(SoTietKiemDTO stk)
        {

           // B1&B2 Tao chuoi ket noi va mo ket noi bang doi tuong ket noi
            OleDbConnection cn;
            cn = Database.ConnectionData();
           // B3 Tao chuoi strSQL thao tac CSDL
            string strSQL;

            strSQL = @"UPDATE SOTIETKIEM  Set HoTenKhachHang=?, CMND=?,DiaChi=?,NgayMoSo=?,SoTienGoi=?,SoLanDaoHan=?,TinhTrangSo=?
                                              ,NgayDaoHan=? Where Maso=?";


           // B4 Thuc Thi chuoi strSQL
            OleDbCommand cmd = new OleDbCommand(strSQL, cn);

            cmd.Parameters.Add("@HoTenKhachHang", OleDbType.VarChar);
            cmd.Parameters.Add("@CMND", OleDbType.VarChar);
            cmd.Parameters.Add("@DiaChi", OleDbType.VarChar);
            cmd.Parameters.Add("@NgayMoSo", OleDbType.Date);
            cmd.Parameters.Add("@SoTienGoi", OleDbType.Double);
            cmd.Parameters.Add("@SoLanDaoHan", OleDbType.Integer);
            cmd.Parameters.Add("@TinhTrangSo", OleDbType.Integer);
            cmd.Parameters.Add("@NgayDaoHan", OleDbType.Date);
            cmd.Parameters.Add("@MaSo", OleDbType.LongVarChar);
            
            cmd.Parameters[0].Value = stk.HoTenKhachHang;
            cmd.Parameters[1].Value = stk.CMND;
            cmd.Parameters[2].Value = stk.DiaChi;
            cmd.Parameters[3].Value = stk.NgayMoSo;
            cmd.Parameters[4].Value = stk.SoTienGoi;
            cmd.Parameters[5].Value = stk.SoLanDaoHan;
            cmd.Parameters[6].Value = stk.TinhTrangSo;
            cmd.Parameters[7].Value = stk.NgayDaoHan;
            cmd.Parameters[8].Value = stk.MaSo;
           

            cmd.ExecuteNonQuery();
           // B5 Dong ket noi
            cn.Close();

        }
        public static void DeleteSoTietKiemTheoMaSo(long maso)
        {
            //B1 &B2 Tao chuoi ket noi va mo doi tuong ket noi bang doi tuong ket noi
            OleDbConnection cn;
            cn = Database.ConnectionData();

            //B2 Tao chuoi strSQL Thao Tac CSDL
            string strSQl = "";
            strSQl = "Delete From SOTIETKIEM Where MaSo = ?";

            //B4 Thuc thi chuoi strSQL
            OleDbCommand cmd = new OleDbCommand(strSQl, cn);

            cmd.Parameters.Add("@MaSo", OleDbType.LongVarChar);
            cmd.Parameters["@MaSo"].Value = maso;
            cmd.ExecuteNonQuery();

            //B5 dong ket noi
            cn.Close();
        }

        //tim kiem so tiet kiem theo ten khach hang
        public static List< SoTietKiemDTO> TimKiemSoTietKiemTheoTenKhachHang(String kh)
        {
            List<SoTietKiemDTO> dsstk = new List<SoTietKiemDTO>();

            //B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
            OleDbConnection cn;
            cn = Database.ConnectionData();

            //B3: Tao chuoi strSQL thao tac CSDL
            string strSQL;
            strSQL = "Select * From SOTIETKIEM Where HoTenKhachHang Like '%" + kh + "%'";

            //B4: Thuc thi chuoi strSQL
            OleDbCommand cmd = new OleDbCommand(strSQL, cn);
            OleDbDataReader dr;
            dr = cmd.ExecuteReader();
            while (dr.Read())
            {
                SoTietKiemDTO stk = new SoTietKiemDTO();

                stk.MaSo = long.Parse(dr["MaSo"].ToString());
                stk.MaLoaiTK = long.Parse(dr["MaLoaiTietKiem"].ToString());
                stk.HoTenKhachHang = (string)dr["HoTenKhachHang"];
                stk.CMND = (string)dr["CMND"];
                stk.DiaChi = (string)dr["DiaChi"];
                stk.NgayMoSo = DateTime.Parse(dr["NgayMoSo"].ToString());
                stk.SoTienGoi = double.Parse(dr["SoTienGoi"].ToString());
                stk.SoLanDaoHan = int.Parse(dr["SoLanDaoHan"].ToString());
                stk.TinhTrangSo = int.Parse(dr["TinhTrangSo"].ToString());
                stk.ThoiHanRutToiThieu = long.Parse(dr["ThoiHanRutToiThieu"].ToString());
                stk.LaiSuat = double.Parse(dr["LaiSuat"].ToString());
                stk.NgayDaoHan = DateTime.Parse(dr["NgayDaoHan"].ToString());
                dsstk.Add(stk);
            }
            //B5: Dong ket noi CSDL

            cn.Close();
            return dsstk;
        }
       
        //lay so tiet kiem ra list theo cau truy van sql voi menh de where
        public static List<SoTietKiemDTO> TimSoTietKiemTheoMenhDeWhere(string sql)
        {
            List<SoTietKiemDTO> dsstk = new List<SoTietKiemDTO>();

            //B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
            OleDbConnection cn;
            cn = Database.ConnectionData();

            //B3: Tao chuoi strSQL thao tac CSDL
            string strSQL;
            strSQL = "Select * From SOTIETKIEM Where " + sql;

            //B4: Thuc thi chuoi strSQL
            OleDbCommand cmd = new OleDbCommand(strSQL, cn);
            OleDbDataReader dr;
            dr = cmd.ExecuteReader();
            while (dr.Read())
            {
                SoTietKiemDTO stk = new SoTietKiemDTO();

                stk.MaSo = long.Parse(dr["MaSo"].ToString());
                stk.MaLoaiTK = long.Parse(dr["MaLoaiTietKiem"].ToString());
                stk.HoTenKhachHang = (string)dr["HoTenKhachHang"];
                stk.CMND = (string)dr["CMND"];
                stk.DiaChi = (string)dr["DiaChi"];
                stk.NgayMoSo = DateTime.Parse(dr["NgayMoSo"].ToString());
                stk.SoTienGoi = double.Parse(dr["SoTienGoi"].ToString());
                stk.SoLanDaoHan = int.Parse(dr["SoLanDaoHan"].ToString());
                stk.TinhTrangSo = int.Parse(dr["TinhTrangSo"].ToString());
                stk.ThoiHanRutToiThieu = long.Parse(dr["ThoiHanRutToiThieu"].ToString());
                stk.LaiSuat = double.Parse(dr["LaiSuat"].ToString());
                stk.NgayDaoHan = DateTime.Parse(dr["NgayDaoHan"].ToString());
                dsstk.Add(stk);
            }
            //B5: Dong ket noi CSDL

            cn.Close();
            return dsstk;
        }
        public static List<SoTietKiemDTO> TimKiemSoTietKiemTheoMaSo(long ms)
        {
            List<SoTietKiemDTO> dsstk = new List<SoTietKiemDTO>();
            string md = "MaSo =" + ms.ToString() + "'";
            dsstk = TimSoTietKiemTheoMenhDeWhere(md);

            return dsstk;
        }
        public static List<SoTietKiemDTO> TimKiemSoTietKiemTheoCMND(String cm)
        {
            List<SoTietKiemDTO> dsstk = new List<SoTietKiemDTO>();
            string sql = "CMND Like '%" + cm + "%'";
            dsstk = TimSoTietKiemTheoMenhDeWhere(sql);

            return dsstk;
        }
        public static List<SoTietKiemDTO> LaySoTietKiemTheoMaLoaiTietKiem(long maloai)
        {
            List<SoTietKiemDTO> ds = new List<SoTietKiemDTO>();
            string sql = "MaLoaiTietKiem = "+maloai.ToString();
            ds=TimSoTietKiemTheoMenhDeWhere(sql);
            return ds;
        }

        public static long SoMo(long maloaitk, int ngay,int th,int nam)
        {
            

                string strSQL =@"SELECT Count(SOTIETKIEM.MaSo) AS CountOfMaSo
                FROM SOTIETKIEM
                WHERE (((Month([NgayMoSo]))="+th.ToString()+ ") AND ((SOTIETKIEM.[TinhTrangSo])=1) AND ((Day([NgayMoSo]))="+ngay.ToString()+") AND ((Year([NgayMoSo]))=2010) AND ((SOTIETKIEM.MaLoaiTietKiem)=11))";

            
           long kq;
          

            kq = Database.ExecuteScalar(strSQL);
            return kq;


        }
        public static long SoDong(long maloaitk, int ngay,int th, int nam )
        {


            string strSQL = @"SELECT Count(MaSo)
                              FROM SOTIETKIEM
                              Where MaLoaiTietKiem =" + maloaitk.ToString() + " AND Month([NgayMoSo])=" + th.ToString() +
                                                    "And TinhTrangSo= 0 And Day([NgayMoSo]) = " + ngay.ToString() + " and Year([NgayMoSo])="+nam.ToString();
            
            long kq;
           
            kq = Database.ExecuteScalar(strSQL);
            return kq;



        }
       
        
       
    }
}
